﻿/*
 * 由SharpDevelop创建。
 * 用户： lucifer
 * 日期: 2011/3/17
 * 时间: 18:24
 * 
 * 要改变这种模板请点击 工具|选项|代码编写|编辑标准头文件
 */
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Text;

using Yidu.Persister.Generator;
using Yidu.Persister.Mapping;
using System.Reflection;

namespace Yidu.Persister {
    /// <summary>
    /// Description of BaseDao.
    /// </summary>
    public class BaseDao : IDao {
        private ISqlGenerator sqlGenerator = new SqlServerSqlGenerator();
        private IDbNameGuesser guesser = new DbNameGuesser();


        #region DAO接口实现方法

        public T Load<T>(params NaVa[] paramObjects) {
            Type entryType = typeof(T);
            TableMappingInfo tableMapping = TableMappingHelper.Instance.GetEntryMapping(entryType);
            if (tableMapping.PrimaryKeys.Count == 0) {

            } else if (tableMapping.PrimaryKeys.Count > 1) {

            }

            if (paramObjects == null || paramObjects.Length == 0) {
                throw new Exception("查询参数不能为空！");
            }

            foreach (NaVa nv in paramObjects) {
                if (nv.Type != NaVaType.Where) {
                    throw new Exception("只能接收Where类型的参数！");
                }
            }

            StringBuilder builder = new StringBuilder();
            builder.Append("select  ");

            bool first = true;
            foreach (var keyValue in tableMapping.FieldMapping) {
                if (first) {
                    first = false;
                    builder.Append(keyValue.Value.FieldName);
                } else {
                    builder.Append(",").AppendLine().Append("\t").Append(keyValue.Value.FieldName);
                }

            }

            builder.AppendLine().Append("  from ").Append(tableMapping.TableName).AppendLine();
            builder.Append("\t").Append("where 1 = 1 ");


            foreach (var nv in paramObjects) {
                FieldMappingInfo field = tableMapping.PropertyMapping[nv.Name];
                if (field == null) {
                    throw new Exception("该条件找不到映射关系！");
                }
                builder.Append(" and ").Append(field.FieldName).Append(" = ").Append("@").Append(field.PropertyName);
            }

            string sqlCmd = builder.ToString();

            List<Dictionary<string, object>> dataList = ListByProperty(sqlCmd, false, paramObjects);
            if (dataList == null || dataList.Count == 0) {
                return default(T);
            } else if (dataList.Count > 1) {

            }
            Dictionary<string, object> dict = dataList[0];
            T t = (T)Activator.CreateInstance(entryType);
            foreach (var keyValue in tableMapping.FieldMapping) {
                FieldMappingInfo field = keyValue.Value;
                object valueObject = dict[field.FieldName];
                if (valueObject is DBNull) {
                    valueObject = null;
                }
                field.PropertyInfo.SetValue(t, valueObject, null);

            }
            return t;

        }


       

        public List<Dictionary<string, object>> List(string sqlKeyOrText, bool dynamicSql, params  object[] paramObjects)
        {
            SqlConnection conn = null;
            SqlCommand command = null;
            SqlDataReader reader = null;

            List<Dictionary<string, object>> dataList = new List<Dictionary<string, object>>();

            try
            {
                conn = DBHelper.Instance.CreateConnection();
                command = new SqlCommand(sqlKeyOrText, conn);
                if (!dynamicSql)
                {
                    if (paramObjects.Length % 2 != 0)
                    {
                        throw new Exception("DbParamter参数不匹配！格式为：name_string,valueObject,name_string,valueObject,......");
                    }
                    for (int i = 0; i < paramObjects.Length; i += 2)
                    {
                        if (paramObjects[i] is string)
                        {
                            command.Parameters.Add(DBHelper.createDbParameter(paramObjects[i].ToString(), paramObjects[i + 1]));
                        }
                        else
                        {
                            throw new Exception("DbParamter参数不匹配！格式为：name_string,valueObject,name_string,valueObject,......");
                        }

                    }
                }

                conn.Open();
                reader = command.ExecuteReader();
                DataTable table = reader.GetSchemaTable();

                IList<string> cols = new List<string>();
                foreach (DataRow myField in table.Rows)
                {
                    object c = myField["ColumnName"];
                    if (c == null)
                    {
                        throw new Exception(string.Format("列名称有重复，无法确定唯一的列名称！列序号={0}", myField["ColumnOrdinal"]));
                    }
                    if (cols.Contains(c.ToString()))
                    {
                        throw new Exception(string.Format("列名称有重复，无法确定唯一的列名称！列序号={0}", myField["ColumnOrdinal"]));
                    }
                    cols.Add(c.ToString());

                }



                while (reader.Read())
                {
                    Dictionary<string, object> dict = new Dictionary<string, object>();
                    foreach (string key in cols)
                    {
                        dict.Add(key, reader[key]);
                    }
                    dataList.Add(dict);
                }
            }
            finally
            {
                DBHelper.CancelQuiet(command);
                DBHelper.CloseQuiet(reader, conn);
            }
            return dataList;
        }


        public List<Dictionary<string, object>> ListByProperty(string sqlKeyOrText, bool dynamicSql, params NaVa[] paramObjects) {
            SqlConnection conn = null;
            SqlCommand command = null;
            SqlDataReader reader = null;

            List<Dictionary<string, object>> dataList = new List<Dictionary<string, object>>();

            try {
                conn = DBHelper.Instance.CreateConnection();
                command = new SqlCommand(sqlKeyOrText, conn);
                if (!dynamicSql) {

                    foreach (var nv in paramObjects) {
                        command.Parameters.Add(DBHelper.createDbParameter(nv.Name, nv.Value));
                    }
                    
                
                }

                conn.Open();
                reader = command.ExecuteReader();
                DataTable table = reader.GetSchemaTable();

                IList<string> cols = new List<string>();
                foreach (DataRow myField in table.Rows) {
                    object c = myField["ColumnName"];
                    if (c == null) {
                        throw new Exception(string.Format("列名称有重复，无法确定唯一的列名称！列序号={0}", myField["ColumnOrdinal"]));
                    }
                    if (cols.Contains(c.ToString())) {
                        throw new Exception(string.Format("列名称有重复，无法确定唯一的列名称！列序号={0}", myField["ColumnOrdinal"]));
                    }
                    cols.Add(c.ToString());

                }



                while (reader.Read()) {
                    Dictionary<string, object> dict = new Dictionary<string, object>();
                    foreach (string key in cols) {
                        dict.Add(key, reader[key]);
                    }
                    dataList.Add(dict);
                }
            } finally {
                DBHelper.CancelQuiet(command);
                DBHelper.CloseQuiet(reader, conn);
            }
            return dataList;
        }

        public T Load<T>(object primaryKey)
        {
            Type entryType = typeof(T);
            TableMappingInfo tableMapping = TableMappingHelper.Instance.GetEntryMapping(entryType);
            if (tableMapping.PrimaryKeys.Count == 0)
            {

            }
            else if (tableMapping.PrimaryKeys.Count > 1)
            {

            }

            StringBuilder builder = new StringBuilder();
            builder.Append("select  ");

            bool first = true;
            foreach (var keyValue in tableMapping.FieldMapping)
            {
                if (first)
                {
                    first = false;
                    builder.Append(keyValue.Value.FieldName);
                }
                else
                {
                    builder.Append(",").AppendLine().Append("\t").Append(keyValue.Value.FieldName);
                }

            }

            builder.AppendLine().Append("  from ").Append(tableMapping.TableName).AppendLine();
            builder.Append("\t").Append("where ").Append(tableMapping.PrimaryKeys[0]).Append(" = ");
            builder.Append("@primaryKey");

            string sqlCmd = builder.ToString();

            List<Dictionary<string, object>> dataList = List(sqlCmd, false, "primaryKey", primaryKey);
            if (dataList == null || dataList.Count == 0)
            {
                return default(T);
            }
            else if (dataList.Count > 1)
            {

            }
            Dictionary<string, object> dict = dataList[0];
            T t = (T)Activator.CreateInstance(entryType);
            foreach (var keyValue in tableMapping.FieldMapping)
            {
                FieldMappingInfo field = keyValue.Value;
                object valueObject = dict[field.FieldName];
                if (valueObject is DBNull)
                {
                    valueObject = null;
                }
                field.PropertyInfo.SetValue(t, valueObject, null);

            }
            return t;

        }

        public int Save(object entry)
        {
            DbConnection conn = null;
            DbCommand cmd = null;
            try
            {
                conn = DBHelper.Instance.CreateConnection();
                cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;

                StringBuilder builder = new StringBuilder();
                StringBuilder valueBuilder = new StringBuilder();
                TableMappingInfo tableMapping = TableMappingHelper.Instance.GetEntryMapping(entry.GetType());

                builder.Append("insert into ").Append(tableMapping.TableName).AppendLine();
                builder.Append("(").AppendLine();
                valueBuilder.Append("(").AppendLine();
                bool first = true;

                foreach (var keyValue in tableMapping.FieldMapping)
                {
                    object propertyValue = keyValue.Value.PropertyInfo.GetValue(entry, null);

                    if (first)
                    {
                        first = false;
                    }
                    else
                    {
                        builder.Append(",").AppendLine();
                        valueBuilder.Append(",").AppendLine();
                    }
                    builder.Append(keyValue.Value.FieldName);
                    if (propertyValue == null)
                    {
                        propertyValue = DBNull.Value;
                    }

                    valueBuilder.Append("@").Append(keyValue.Value.PropertyName);
                    DbParameter param = DBHelper.createDbParameter(keyValue.Value.PropertyName, propertyValue);
                    cmd.Parameters.Add(param);


                }
                builder.AppendLine().Append(")");
                valueBuilder.AppendLine().Append(")");

                builder.AppendLine().Append("values").AppendLine().Append(valueBuilder.ToString());
                cmd.CommandText = builder.ToString();
                Console.WriteLine(cmd.CommandText);
                conn.Open();

                return cmd.ExecuteNonQuery();
            }
            finally
            {
                DBHelper.CancelQuiet(cmd);
                DBHelper.CloseQuiet(conn);
            }

        }

        public int Update(object entry) {
            DbConnection conn = null;
            DbCommand cmd = null;
            try {
                StringBuilder builder = new StringBuilder();
                TableMappingInfo tableMapping = TableMappingHelper.Instance.GetEntryMapping(entry.GetType());

                if (tableMapping.PrimaryKeys.Count == 0) {

                } else if (tableMapping.PrimaryKeys.Count > 1) {

                }
                string primaryKey = tableMapping.PrimaryKeys[0];

                conn = DBHelper.Instance.CreateConnection();
                cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;

                builder.Append("update ").Append(tableMapping.TableName).AppendLine();
                builder.Append("set").AppendLine();
                bool first = true;

                PropertyInfo primaryKeyPropertyInfo = null;
                
                foreach (var keyValue in tableMapping.FieldMapping) {
                    if (primaryKey.Equals(keyValue.Value.FieldName,StringComparison.OrdinalIgnoreCase)) {
                        primaryKeyPropertyInfo = keyValue.Value.PropertyInfo;
                        continue;
                    }
                    object propertyValue = keyValue.Value.PropertyInfo.GetValue(entry, null);

                    if (first) {
                        first = false;
                    } else {
                        builder.Append(",").AppendLine();
                    }
                    builder.Append(keyValue.Value.FieldName).Append("=@").Append(keyValue.Value.PropertyName);
                    if (propertyValue == null) {
                        propertyValue = DBNull.Value;
                    }
                    DbParameter param = DBHelper.createDbParameter(keyValue.Value.PropertyName, propertyValue);
                    cmd.Parameters.Add(param);


                }
                builder.AppendLine().Append("where ").Append(tableMapping.PrimaryKeys[0]).Append(" = ");
                builder.Append("@primaryKey");

                if (primaryKeyPropertyInfo == null) {
                    throw new Exception("该表没有主键或者主键没有映射到POCO！");
                }
                object primaryKeyValue = primaryKeyPropertyInfo.GetValue(entry, null);
                if (primaryKeyValue == null) {
                    throw new Exception("主键没有值！");
                }
                DbParameter tmp = DBHelper.createDbParameter("primaryKey", primaryKeyValue);
                cmd.Parameters.Add(tmp);

                cmd.CommandText = builder.ToString();

                conn.Open();

                return cmd.ExecuteNonQuery();
            } finally {
                DBHelper.CancelQuiet(cmd);
                DBHelper.CloseQuiet(conn);
            }
        }

        public List<T> List<T>(params NaVa[] paramObjects) {
            Type entryType = typeof(T);
            TableMappingInfo tableMapping = TableMappingHelper.Instance.GetEntryMapping(entryType);

            if (paramObjects == null || paramObjects.Length == 0) {
                throw new Exception("查询参数不能为空！");
            }

            foreach (NaVa nv in paramObjects) {
                if (nv.Type != NaVaType.Where) {
                    throw new Exception("只能接收Where类型的参数！");
                }
            }

            StringBuilder builder = new StringBuilder();
            builder.Append("select  ");

            bool first = true;
            foreach (var keyValue in tableMapping.FieldMapping) {
                if (first) {
                    first = false;
                    builder.Append(keyValue.Value.FieldName);
                } else {
                    builder.Append(",").AppendLine().Append("\t").Append(keyValue.Value.FieldName);
                }

            }

            builder.AppendLine().Append("  from ").Append(tableMapping.TableName).AppendLine();
            builder.Append("\t").Append("where 1 = 1 ");


            foreach (var nv in paramObjects) {
                FieldMappingInfo field = tableMapping.PropertyMapping[nv.Name];
                if (field == null) {
                    throw new Exception("该条件找不到映射关系！");
                }
                builder.Append(" and ").Append(field.FieldName).Append(" = ").Append("@").Append(field.PropertyName);
            }

            string sqlCmd = builder.ToString();

            List<Dictionary<string, object>> dataList = ListByProperty(sqlCmd, false, paramObjects);

            List<T> tmpList = new List<T>();

            foreach (var dict in dataList) {
                T t = (T)Activator.CreateInstance(entryType);
                foreach (var keyValue in tableMapping.FieldMapping) {
                    FieldMappingInfo field = keyValue.Value;
                    object valueObject = dict[field.FieldName];
                    if (valueObject is DBNull) {
                        valueObject = null;
                    }
                    field.PropertyInfo.SetValue(t, valueObject, null);
                }
                tmpList.Add(t);
            }

            return tmpList;

        }


        public int Delete(object entry) {
            DbConnection conn = null;
            DbCommand cmd = null;
            try {
                StringBuilder builder = new StringBuilder();
                TableMappingInfo tableMapping = TableMappingHelper.Instance.GetEntryMapping(entry.GetType());

                if (tableMapping.PrimaryKeys.Count == 0) {
                    throw new Exception("该表没有主键映射，不能使用该方式删除！");
                } else if (tableMapping.PrimaryKeys.Count > 1) {
                    throw new Exception("找到多个该表的主键映射，不能使用该方式删除！");
                }
                string primaryKey = tableMapping.PrimaryKeys[0];

                conn = DBHelper.Instance.CreateConnection();
                cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;

                builder.Append("delete from ").Append(tableMapping.TableName).Append(" where ");
                builder.Append(primaryKey).Append("=@primaryKey");
                
                PropertyInfo primaryKeyPropertyInfo = null;

                foreach (var keyValue in tableMapping.FieldMapping) {
                    if (primaryKey.Equals(keyValue.Value.FieldName, StringComparison.OrdinalIgnoreCase)) {
                        primaryKeyPropertyInfo = keyValue.Value.PropertyInfo;
                        break;
                    }
                }

                object primaryKeyValue = primaryKeyPropertyInfo.GetValue(entry, null);
                if (primaryKeyValue == null) {
                    throw new Exception("主键值不能为空！");
                }
                DbParameter tmp = DBHelper.createDbParameter("primaryKey", primaryKeyValue);
                cmd.Parameters.Add(tmp);

                cmd.CommandText = builder.ToString();

                conn.Open();

                return cmd.ExecuteNonQuery();
            } finally {
                DBHelper.CancelQuiet(cmd);
                DBHelper.CloseQuiet(conn);
            }
        }

        public void Begin()
        {
           
        }

        public void Commit()
        {
            
        }

        public void Rollback() {
            
        }

        #endregion
    }
}
